﻿#include "stdafx.h"
#include "GradeDB.h"


GradeDB::GradeDB()
{
}
Grade GradeDB::selectByGradeId(const std::string &id)
{
	Grade grade;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		res = stmt->executeQuery("SELECT * from grade where grade_id=" + id);

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string gradeId = res->getString(1);
			double ordinary = res->getDouble(2);
			double gfinal = res->getDouble(3);
			double total = res->getDouble(4);
			grade.setGradeId(gradeId);
			grade.setOrdinary(ordinary);
			grade.setGfinal(gfinal);
			grade.setTotal(total);
		}
		delete res;
		delete stmt;
		delete con;
		return grade;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
list<Grade> GradeDB::selectAll()
{
	list<Grade> list;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		res = stmt->executeQuery("SELECT * from grade");

		while (res->next()) {
			Grade grade;
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string gradeId = res->getString(1);
			double ordinary = res->getDouble(2);
			double gfinal = res->getDouble(3);
			double total = res->getDouble(4);
			grade.setGradeId(gradeId);
			grade.setOrdinary(ordinary);
			grade.setGfinal(gfinal);
			grade.setTotal(total);
			list.push_back(grade);
		}
		delete res;
		delete stmt;
		delete con;
		return list;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
list<Grade> GradeDB::selectAllByGradeId(const std::string &id)
{
	list<Grade> list;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		res = stmt->executeQuery("SELECT * from grade where grade_id=" + id);

		while (res->next()) {
			Grade grade;
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string gradeId = res->getString(1);
			double ordinary = res->getDouble(2);
			double gfinal = res->getDouble(3);
			double total = res->getDouble(4);
			grade.setGradeId(gradeId);
			grade.setOrdinary(ordinary);
			grade.setGfinal(gfinal);
			grade.setTotal(total);
			list.push_back(grade);
		}
		delete res;
		delete stmt;
		delete con;
		return list;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

void GradeDB::deleteByGradeId(const std::string &i)
{
	list<Grade> list;
	try {
		Connection *con;
		Statement *stmt;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		stmt->executeUpdate("delete from grade where grade_id='" + i + "'");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
void GradeDB::update(Grade &g)//删除成绩
{
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		string s = "update grade set ordinary=" + doubleToString(g.getOrdinary()) + ",final=" + doubleToString(g.getGfinal()) + ",total=" + doubleToString(g.getTotal()) + " where grade_id='" + g.getGradeId() + "'";
		//cout << s << endl;
		stmt->executeUpdate(s);
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
void GradeDB::insert(Grade &g) {
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names utf8");
		stmt->executeUpdate("insert into grade values('"+g.getGradeId()+"'," + doubleToString(g.getOrdinary()) + "," + doubleToString(g.getGfinal()) + "," + doubleToString(g.getTotal())+")");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

GradeDB::~GradeDB()
{
}

